/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.mycompany.h2;

import com.mycompany.h2.StudentService;
import com.mycompany.h2.StudentInfo;
import com.mycompany.h2.StudentDAO;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.h2.tools.Server;



/**
 *
 * @author JSJSYS
 */
public class h2Test {
    //驱动类，H2jar包中可以找到
    static final String DRIVER_CLASS = "org.h2.Driver";
    //数据库连接URL，当前连接的是E:/H2目录下的mydb数据库
   // static final String JDBC_URL = "jdbc:h2:E:/H23/mydb";
    static final String JDBC_URL = "jdbc:h2:tcp://localhost/C:/Users/JSJSYS/test";
    // static final String JDBC_URL = "jdbc:h2:tcp://localhost/mem:mydb";
    //用户名
    static final String USER = "sa";
    //密码
    static final String PASSWORD = "yjy";
    
    
    
    
    public static void main(String[] args){
        // create server
        
        initServer();
        
        jdbcDemo();
 
         // ++++++++++++++++++++++++++++++++ormlite
         
        ormDemo(); 
    }
        
    
    private static int num =1;
    private static int getId(){
        return num++  ;
    }
    
    
    
    private static void show( Statement stmt) throws Exception{
        //查询
        ResultSet rs = stmt.executeQuery("SELECT * FROM stu_info");
        while (rs.next()) {
            p(rs.getString(1) + "\t" + rs.getString("name") + "\t" + rs.getString("sex")+"\t"+rs.getString(4));
        }
    }
    

    
    public static void jdbcDemo(){
        try {
            // 加载驱动
            Class.forName(DRIVER_CLASS);
            // 根据连接URL，用户名，密码获取数据库连接
            Connection conn = DriverManager.getConnection(JDBC_URL, USER, PASSWORD);
            // 创建一个Statement 
            Statement stmt = conn.createStatement();
            stmt.execute("DROP TABLE IF EXISTS stu_info");
            //创建stu_info表
            stmt.execute("CREATE TABLE stu_info(id VARCHAR(36) PRIMARY KEY,name VARCHAR(100),sex VARCHAR(4),student_id VARCHAR(50))");
            //新增4 条
            stmt.executeUpdate("INSERT INTO stu_info VALUES('" + getId() + "','程凌霄','男','20151101066')");
            stmt.executeUpdate("INSERT INTO stu_info VALUES('" + getId() + "','陈秋云','女','20151101026')");
            stmt.executeUpdate("INSERT INTO stu_info VALUES('" + getId() + "','黍路','女','20151101056')");
            stmt.executeUpdate("INSERT INTO stu_info VALUES('" + getId() + "','田梦','女','20151101096')");
       
            p("++++++++++++++++++++++++++++++++++++++显示刚保存的四条数据：");
            
            show(stmt);
            p("");
            p(""); 
            p("");   
            //删除 陈秋云
            stmt.executeUpdate("DELETE FROM stu_info WHERE name='陈秋云'");
            p("++++++++++++++++++++++++++++++++++++++删除 陈秋云 后，显示结果：");
            show(stmt);
            
            p("");
            p("");
            //修改 黍路 === >黎路
            stmt.executeUpdate("UPDATE stu_info SET name='黎路' WHERE name='黍路'");
            p("=+++++++++++++++++++++++++++++++++修改 黍路 === >黎路,显示结果：");
            show(stmt);

      
        
        
            //释放stmt
            stmt.close();
        
            
            //关闭连接
            conn.close();
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(h2Test.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(h2Test.class.getName()).log(Level.SEVERE, null, ex);
        } catch (Exception ex) {
            Logger.getLogger(h2Test.class.getName()).log(Level.SEVERE, null, ex);
        }finally{
            p("--- end ---");
        }
    }
    

    
    public static void p(String a){
        System.out.println(a);
    }

    private static void ormDemo() {
        p("");
            p("");
            p("");
            p("");
            p("++++++++++++++++++++++++以下是ormlite 操作H2 ++++++++++++++++++++++++");
            StudentDAO studentDAOUtil =new StudentDAO(s);
            StudentService service = new StudentService(studentDAOUtil.getStudentDAO());
            StudentInfo info = new StudentInfo();
            info.setId(817);
            info.setName("lilei");
            info.setSex("male");
            info.setStudentId("1111FFF111");
            service.save(info);
            //显示目前学生表的所有的记录,并取得第一条数据
            StudentInfo first = showAll(service).get(0);
            p("");
            p("");
            p("===========================更新第一条数据：姓名 "+first.getName()+" 改成 刘德华");
            first.setName("刘德华");
            // update
            service.update(first);
            // show all
            showAll(service);
            p("");
            p("");
            p("============================删除第一条数据："+first.getName());
            // 删除第一条
            service.del(first);
            // show all
            showAll(service);

            p("");
            p("");
            p("============================增加2条数据：" );
            StudentInfo one = new StudentInfo();
            one.setId(7);
            one.setName("张学友");
            one.setSex("男");
            one.setStudentId("199210106666");
            service.save(one);// save

            StudentInfo two = new StudentInfo();
            two.setId(8);
            two.setName("晧月星");
            two.setSex("女");
            two.setStudentId("199210108888");
            service.save(two);// save
            showAll(service);

            studentDAOUtil.close();
    }
    
    
    public static List<StudentInfo> showAll(StudentService service){
        p("=============================显示目前学生表的所有的记录： ");
        List<StudentInfo> list =  service.queryAll();
        for(StudentInfo info:list){
            p(""+info.getId()+"\t"+info.getName()+"\t"+info.getSex()+"\t"+info.getStudentId());
        }
        return list;
    }

    static Server s=null;
    private static void  initServer(){
        try { 
            s=  Server.createTcpServer().start();
            p(" create H2 server successfully!!!!!!! ");
        } catch (SQLException ex) {
            Logger.getLogger(h2Test.class.getName()).log(Level.SEVERE, null, ex);
            p(" create H2 server failure ");
        }
        
    }
    
}

